﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.Common;
using System.Threading;
using Dataglot.Utils;

namespace Dataglot
{
    public interface IGenericCommand
    {
        string ConnectionString { get; }
        void ComposeSql(string sql);
        DbParameter AddParameter(string name, DbType type);
        void Dispose();
        string Compose(string sql);
        void Set(string name, object value);
        DbDataReader ExecuteReader();
        DbDataReader ExecuteReader(CommandBehavior behavior);
        int ExecuteNonQuery();
        object ExecuteScalar();
        bool IsInitialized();
    }

    /// <summary>
    /// Connection and command optimized for convenience and cross db support.
    /// </summary>
    /// <remarks>
    /// Reporting query performance depends on the underlying DB, not the data access API.
    /// So most queries will be provider agnostic unlike the hit recorders which 
    /// need to be very fast to avoid performance hit to the host application.
    /// </remarks>
    public class GenericCommand:IDisposable, IGenericCommand
    {
        //If turn on this trace, you should get something close to attaching a debugger to MS-SQL
        private readonly TraceSql trace = new TraceSql();

        protected DbCommand command;

        protected DataFactory factory;
        protected DbConnection con = null;

        protected DbTransaction transaction;
        protected bool needToDisposeOfConnection = true;
        private bool isUsingSharedConnection = false;

        protected DbConfigUtils config = new DbConfigUtils();

        public string ConnectionString
        {
            get
            {
                return con == null ? "" : con.ConnectionString;
            }
        }

        /*
        public GenericCommand()
        {
            factory = new DataFactory(config.OleDbConnectionString(), DbConfigUtils.ConnectionString());
            command = factory.Command();
            command.Connection = con;
            OpenConnection();
        }
         */

        public GenericCommand(DataFactory targetFactory)
        {
             factory = targetFactory;
             command = factory.ProviderFactory.CreateCommand();
             command.Connection = con;
             OpenConnection();
        }
        
        public GenericCommand(DataFactory targetFactory, DbConnection sharedConnection, DbTransaction transaction)
        {
            isUsingSharedConnection = true;
            if(sharedConnection==null)
                throw new ArgumentException("sharedConnection is null & is required");
            factory = targetFactory;
            command = factory.ProviderFactory.CreateCommand();
            con = sharedConnection;
            command.Transaction = transaction;
            command.Connection = sharedConnection;
        }

        public void ComposeSql(string sql)
        {
            //Switch to ? or @ depending
            command.CommandText = ProcessNameSpaceMakersParameterTokens(sql);
        }

        /// <summary>
        /// Warning! This assumes any token starting with @ is a parameter
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string ProcessNameSpaceMakersParameterTokens(string sql)
        {
            //if (config == null)
            //    config = new DbConfigUtils(AppSettings, ConnectionStrings);

            sql = sql.Replace("{$ns}", config.Namespace());

            if (sql.Contains("{$ns}"))
            {
                throw new InvalidOperationException(
                    "Please add to appSettings in the App.Config or web.config file the following: <add key=\"Dataglot.Namespace\" value=\"yourPrefix\"/>");
            }

            if (factory.SupportInfo.Brand== DbBrand.OleDb)
            {
                if (sql.Contains("@"))
                {
                    TraceApp.Verbose(() => "Found named parameters (contains @), changing to positional");
                    string[] tokens = sql.Split(new[] { " " }, StringSplitOptions.RemoveEmptyEntries);

                    for (int i = 0; i < tokens.Length; i++)
                    {
                        if (tokens[i].StartsWith("@"))
                            tokens[i] = "?";

                    }
                    return String.Join(" ", tokens);
                }
                else
                {
                    TraceApp.Verbose(() => "Didn't find any named parameters");
                    return sql;
                }
            }
            TraceApp.Verbose(() => "Treating SQL as native, not changing named to positional parameters");
            return sql;
        }


        public DbParameter AddParameter(string name, DbType type)
        {
            TraceApp.Verbose(()=>"Adding " + name + " with type of  " + type.ToString());
            DbParameter parameter = factory.ProviderFactory.CreateParameter(name, type);
            command.Parameters.Add(parameter);
            return command.Parameters[name];
        }

        //public DbParameter AddOutputParameter(string name, DbType type)
        //{
        //    TraceApp.Verbose(() => "Adding output param: " + name + " with type of  " + type.ToString());
        //    DbParameter parameter = factory.Parameter(name, type);
        //    //SqlCompact doesn't like .Output
        //    parameter.Direction = ParameterDirection.ReturnValue;
        //    command.Parameters.Add(parameter);
        //    return command.Parameters[name];
        //}

        protected void ValidateSql()
        {
            if (command.CommandText.Contains("{$ns}"))
            {
                throw new InvalidOperationException(
                    "Please add to appSettings in the App.Config or web.config file the following: <add key=\"Dataglot.Namespace\" value=\"yourPrefix\"/>");
            }
        }

        protected void OpenConnection() 
        {
            if (con == null)
            {
               TraceApp.Verbose("Creating a connection");
                con = factory.ProviderFactory.CreateConnection();
            }          

            if (con.State == ConnectionState.Closed)
            {
                TraceApp.Verbose("Opening a connection");
                con.Open();
                //TODO: Need to make sure we're not running in serializable...
                //con.BeginTransaction(IsolationLevel.ReadCommitted)
            }

            if(con is SqlConnection)
            {
                TraceApp.Verbose("Registering a SqlConnection Info Message handler");
                SqlConnection sqlcon = (SqlConnection) con;
                sqlcon.InfoMessage += SqlconInfoMessage;
            }
            if(command.Connection == null)
                command.Connection = con;
        }

        void SqlconInfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            trace.TraceSqlInfoMessageEventArgs(e);
        }

        public void Dispose()
        {
            if (!isUsingSharedConnection)
            {
                if (transaction != null)
                {
                    if (transaction.Connection != null)
                    {
                        TraceApp.Verbose("Committing transaction");
                        transaction.Commit();
                    }
                    
                }


                //if (chartCommand != null)
                //    chartCommand.Dispose();

                if (con != null)
                {
                    if (con.State == ConnectionState.Open)
                    {
                        TraceApp.Verbose("Closing connection");
                        con.Close();
                    }
                    con.Dispose();
                }
            }
        }

        /// <summary>
        /// Process namespace token
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string Compose(string sql)
        {
            return  sql.Replace("{$ns}", config.Namespace());
        }

        public void Set(string name, object value)
        {
            try
            {
                command.Parameters[name].Value = value;
            }
            catch (System.IndexOutOfRangeException)
            {
                TraceApp.Error(()=>"Was looking for " + name + ", but Parameters Collection contains only:");
                foreach (DbParameter c in command.Parameters)
                {
                    TraceApp.Error(c.ParameterName);
                }
                TraceApp.Error(() => "Sql: " + command.CommandText);
                throw;
            }
            
        }

        public DbDataReader ExecuteReader()
        {
            ValidateSql();
            OpenConnection();
            trace.TraceCommand(command);
            return command.ExecuteReader();
        }

        public DbDataReader ExecuteReader(CommandBehavior behavior)
        {
            ValidateSql();
            OpenConnection();
            try
            {
                trace.TraceCommand(command);
                return command.ExecuteReader(behavior);
            }
            catch (SqlException ex)
            {
                trace.TraceSqlException(ex, command);
                throw;
            }
            catch(DbException dbEx)
            {
                trace.TraceDbException(dbEx, command);
                throw;
            }
        }

        public int ExecuteNonQuery()
        {
            ValidateSql();
            OpenConnection();
            trace.TraceCommand(command);
            return command.ExecuteNonQuery();
        }

        public object ExecuteScalar()
        {
            ValidateSql();
            OpenConnection();
            trace.TraceCommand(command);
            return command.ExecuteScalar();
        }

        public bool IsInitialized()
        {
            if (string.IsNullOrEmpty(command.CommandText)) return false;
            return true;
        }
    }
}
